﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_FB02(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_FB02(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_FB02(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_FB02(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertICC_FB02(dwData, 1);
            }
            UpdateICC_FB02(dwLocal);

        }
        public void InsertICC_FB02(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://20140501
                    or1.CommandText = "INSERT INTO ICC_FB02(LSNAME1, FBYEAR, FBMONTH, FBDAY, FSARTGRP, FSMATART, FBLO, FBLB,FBAUF,FBARCODE, FBART, FSABEZ, FBFARBE, FBGROE, FBME, FBSTDPRICE, FBEKPRICE, FBREKPRICE, FBEKVALUE, FBSTDVALUE)"
                     + " SELECT LSNAME1 AS LSNAME1,'2014' AS FBYEAR,'12' AS FBMONTH,'30' AS FBDAY,  SUBSTR(S178.SDDATEN,2,20) AS FSARTGRP, SUBSTR(S649.SDDATEN,2,20) AS FSMATART,"
                     + " FBLO,FBLB,"
                     + "FCBESTKRS||FCBESTLFD AS FBAUF,REPLACE(FBART||FBFARBE||FBGROE,' ','')BBARCODE,"
                     +"FBART,FSABEZ,FBFARBE,FBGROE,FBME,"
                     + "0 AS FBSTDPRICE,0 AS FBEKPRICE,0 AS FBREKPRICE,0 AS FBEKVALUE,0 AS FBSTDVALUE"
                    + " FROM FBST B JOIN FSSTAMM M ON FBART=FSART AND FBVAR=FSVAR"
                    + " LEFT JOIN SDDAT S178 ON S178.SDSA='0178' AND '00'||M.FSARTGRP=SUBSTR(S178.SDKEY,1,5) AND SUBSTR(S178.SDKEY,24,1)='V'"
                    + " LEFT JOIN SDDAT S649 ON S649.SDSA='0649' AND '00'||M.FSMATART=SUBSTR(S649.SDKEY,1,6) AND SUBSTR(S649.SDKEY,24,1)='V'"
                    + " JOIN FCHAR C ON FBMANDANT=FCMANDANT AND FBCHAR=FCCHAR"
                    + " JOIN ABPOS ON ABPMANDANT=FCMANDANT AND FCAUFTYP='V' AND ABPAUFKRS=C.FCBESTKRS AND ABPAUFLFD=FCBESTLFD AND ABPAUFPOS=FCBESTPOS"
                    + " LEFT JOIN LIEFDAT LS ON LSMANDANT=ABPMANDANT AND LSLIEFNR=ABPLIEFNR"
                    + " WHERE FBMANDANT =:FBMANDANT AND FBART LIKE '"+dwData["FBART"].ToString().Trim()+"%'";
                    or1.Parameters.Add("FBMANDANT", OracleType.NVarChar).Value = dwData["FBMANDANT"].ToString();
                    break;
                case 2://20140501
                    or1.CommandText = "INSERT INTO ICC_FB02(LSNAME1, FBYEAR, FBMONTH, FBDAY, FSARTGRP, FSMATART, FBLO, FBLB,FBAUF,FBARCODE, FBART, FSABEZ, FBFARBE, FBGROE, FBME, FBSTDPRICE, FBEKPRICE, FBREKPRICE, FBEKVALUE, FBSTDVALUE)"
                     + " SELECT LSNAME1 AS LSNAME1,'2014' AS FBYEAR,'12' AS FBMONTH,'30' AS FBDAY,"
                     +" SUBSTR(S178.SDDATEN,2,20) AS FSARTGRP, SUBSTR(S649.SDDATEN,2,20) AS FSMATART,FBLO,FBLB,"
                     +"FCBESTKRS||FCBESTLFD AS FBAUF,REPLACE(FBART||FBFARBE||FBGROE,' ','') AS FBARCODE,"
                     +"FBART,FSABEZ,FBFARBE,FBGROE,FBME,"
                     +"0 AS FBSTDPRICE,0 AS FBEKPRICE,EKPPRME*0.001 AS FBREKPRICE,0 AS FBEKVALUE,0 AS FBSTDVALUE"
                     +" FROM FBST B JOIN FSSTAMM M ON FBART=FSART AND FBVAR=FSVAR"
                     +" LEFT JOIN SDDAT S178 ON S178.SDSA='0178' AND '00'||M.FSARTGRP=SUBSTR(S178.SDKEY,1,5) AND SUBSTR(S178.SDKEY,24,1)='V'"
                     +" LEFT JOIN SDDAT S649 ON S649.SDSA='0649' AND '00'||M.FSMATART=SUBSTR(S649.SDKEY,1,6) AND SUBSTR(S649.SDKEY,24,1)='V'"
                     +" JOIN FCHAR C ON FBMANDANT=FCMANDANT AND FBCHAR=FCCHAR"
                     +"  JOIN EKPOS ON EKPMANDANT=FCMANDANT AND FCAUFTYP='B' AND EKPSA='PO' AND EKPAUFKRS=C.FCBESTKRS AND EKPAUFLFD=FCBESTLFD AND EKPAUFPOS=FCBESTPOS"
                     +"  LEFT JOIN LIEFDAT LS ON LSMANDANT=EKPMANDANT AND LSLIEFNR=EKPLIEFNR"
                    + " WHERE FBMANDANT =:FBMANDANT AND FBART LIKE '" + dwData["FBART"].ToString().Trim() + "%'";
                    or1.Parameters.Add("FBMANDANT", OracleType.NVarChar).Value = dwData["FBMANDANT"].ToString();
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public void DeleteICC_FB02(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_FB02 X";
                    break;
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
        public DataSet QueryICC_FB02(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_FB02  WHERE  FBART LIKE '" + dwData["FBART"].ToString().Trim() + "%'";
                    break; 
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_FB02";
            Return = Result;
            return Return;
        }
        public void UpdateICC_FB02(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1: 
                    break; 
            }
            Connect2Proxy(or1);
        }
    }
}
